package net.wantongtech.xa.uploadbasicinfo.dao;

import com.google.common.collect.Maps;
import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.qxsjz.entity.IntegratedTransOut;
import com.wtkj.vo.dzzf.base.qxsjz.entity.PortalTransOut;
import com.wtkj.vo.dzzf.share.staticvalue.CardStaticValue;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 修改渠道编号DAO实现
 *
 * @author MaShibo
 * 日期 :  2020/1/8
 */
@Repository
public class AlterChannelTypeDao extends BaseDataTemplate {


    public List<PortalTransOut> queryNoChannelTypeTrans() {
        final String sql =
                "SELECT *\n" +
                        "  FROM TR_PORTAL_TRANSOUT TR\n" +
                        " WHERE TR.CHANNELTYPE IS NULL\n" +
                        "   AND (TR.ETCCARDTYPE NOT IN (22, 24) OR TR.ETCCARDTYPE IS NULL)";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(PortalTransOut.class));
    }


    public List<PortalTransOut> queryErrorChannelTypeTransOutTrans() {
        final String sql = "SELECT *\n" +
                "  FROM TR_PORTAL_TRANSOUT S\n" +
                " WHERE S.INTEGRATEDID IN\n" +
                "       (SELECT TR.INTEGRATEDID\n" +
                "          FROM TR_INTEGRATED_TRANSOUT TR\n" +
                "         WHERE (TR.MESSAGEID, TR.CHANNELTYPE) IN\n" +
                "               (SELECT S.MSGID, S.CHANNELTYPE\n" +
                "                  FROM ETC_MESSAGESEND S\n" +
                "                 WHERE S.LISTID IN\n" +
                "                       ('3BE6959FA6E4BBC23377D534C142A84F',\n" +
                "                        '4CFD7D89C5FE206594A354CCC09AB9E5',\n" +
                "                        '77962A612C0D9ABAD443E245889EAAD2',\n" +
                "                        '3D1ED82D6610720B09D78CB15934815C',\n" +
                "                        'B6E35BB398A9D991ABB21D2D0A02F3D7',\n" +
                "                        'A5E75A6C640E5B9844B58B1B5C58D456',\n" +
                "                        '96CAFAA8FA565456B7DAEAB3F0676950')))";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(PortalTransOut.class));
    }


    /**
     * 根据OBU合同序列号查询卡号
     *
     * @param obuId OBU合同序列号
     * @return 卡号
     */
    public Optional<String> queryCardNoByObuId(String obuId) {
        final String sql =
                "SELECT *\n" +
                        "  FROM (SELECT c.cardnetid || c.ecardno\n" +
                        "          FROM ac_obu a\n" +
                        "          JOIN ac_cardinfo c\n" +
                        "            ON a.cardinfoid = c.cardinfoid\n" +
                        "         WHERE a.contractno = ?\n" +
                        "           AND a.state IN (?, ?)\n" +
                        "         ORDER BY a.opton DESC)\n" +
                        " WHERE rownum = 1";
        return jdbcTemplate.query(sql, rs -> {
                    if (rs.next()) {
                        return Optional.of(rs.getString(1));
                    }
                    return Optional.empty();
                }, obuId, CardStaticValue.EcardStatus.EcardState_ZC,
                CardStaticValue.EcardStatus.EcardState_JY);
    }


    /**
     * 根据卡号查询原始交易中卡片的信息
     *
     * @param eCardNo 16位卡号
     * @return 卡片信息
     */
    public CardInfo queryCardInfoByNo(String eCardNo) {
        final String sql =
                "SELECT *\n" +
                        "  FROM (SELECT a.accountid,\n" +
                        "               f.fundaccountname,\n" +
                        "               a.channeltype,\n" +
                        "               a.signsource,\n" +
                        "               a.privilegetype,\n" +
                        "               a.ecardtype,\n" +
                        "               a.opton\n" +
                        "          FROM ac_cardinfo a\n" +
                        "          LEFT JOIN ac_account t\n" +
                        "            ON a.accountid = t.accountid\n" +
                        "          LEFT JOIN ac_fundaccount f\n" +
                        "            ON t.fundaccountid = f.fundaccountid\n" +
                        "         WHERE a.ecardno = ?\n" +
                        "        UNION ALL\n" +
                        "        SELECT a.accountid,\n" +
                        "               f.fundaccountname,\n" +
                        "               a.channeltype,\n" +
                        "               a.signsource,\n" +
                        "               a.privilegetype,\n" +
                        "               a.ecardtype,\n" +
                        "               a.opton\n" +
                        "          FROM ac_cardinfo a\n" +
                        "          JOIN ac_cardveh v\n" +
                        "            ON a.cardinfoid = v.cardinfoid\n" +
                        "          LEFT JOIN ac_account t\n" +
                        "            ON a.accountid = t.accountid\n" +
                        "          LEFT JOIN ac_fundaccount f\n" +
                        "            ON t.fundaccountid = f.fundaccountid\n" +
                        "         WHERE v.ecardno = ?)\n" +
                        " ORDER BY opton DESC";
        return jdbcTemplate.query(sql, rs -> {
            if (rs.next()) {
                CardInfo cardInfo = new CardInfo();
                cardInfo.setAccountId(rs.getString(1));
                cardInfo.setName(rs.getString(2));
                cardInfo.setChannelType(rs.getString(3));
                cardInfo.setSignSource(rs.getString(4));
                cardInfo.setPrivilegeType(rs.getString(5));
                cardInfo.setECardType((Integer) JdbcUtils.getResultSetValue(rs, 6, Integer.class));
                return cardInfo;
            }
            return null;
        }, eCardNo, eCardNo);
    }


    public List<PortalTransOut> queryErrorPortalTransoutInfo() {
        final String sql =
                "SELECT * from tr_portal_transout a\n" +
                        "WHERE a.transactionid IN \n" +
                        "('G5611530010024100102020010118100098',\n" +
                        "'S0026310010005100102020010409200664',\n" +
                        "'S0026310010003100102020010409200707',\n" +
                        "'S0026310010004100102020010409100740',\n" +
                        "'S0026310010001100102020010409200165',\n" +
                        "'S0026310010006100102020010409100594',\n" +
                        "'S0026310010002100102020010409100514',\n" +
                        "'S0026310010006200202020010417100931',\n" +
                        "'S0026310010002200102020010418100232',\n" +
                        "'S0026310010004200102020010418100098',\n" +
                        "'S0026310010005200102020010418200034',\n" +
                        "'S0026310010003200102020010418100147')";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(PortalTransOut.class));
    }


    public Map<String, IntegratedTransOut> queryIntegratedTransOutMap() {
        final String sql =
                "SELECT *\n" +
                        "  FROM TR_INTEGRATED_TRANSOUT TR\n" +
                        " WHERE TR.INTEGRATEDID IN ('ING12020010200305403739',\n" +
                        "                           'ING12020010500300208119',\n" +
                        "                           'ING12020010400303207929',\n" +
                        "                           'ING12020010300303205421',\n" +
                        "                           'ING12020010200301100484',\n" +
                        "                           'ING12020010300303405511')";
        BeanPropertyRowMapper<IntegratedTransOut> rowMapper = BeanPropertyRowMapper.newInstance(IntegratedTransOut.class);
        return jdbcTemplate.query(sql, resultSet -> {
            Map<String, IntegratedTransOut> integratedTransOutMap = Maps.newConcurrentMap();
            int rowNum = 1;
            while (resultSet.next()) {
                IntegratedTransOut integratedTransOut = rowMapper.mapRow(resultSet, rowNum);
                if (integratedTransOut != null) {
                    integratedTransOutMap.put(integratedTransOut.getIntegratedId(), integratedTransOut);
                }
                rowNum++;
            }
            return integratedTransOutMap;
        });
    }
}
